package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.canin.model.Breed;

public class BreedDAO
{
	private Connection conn = null;

	public BreedDAO(Connection conn)
	{
		this.conn = conn;
	}
	
	public boolean createNewBreed( Breed breed ) throws Exception
	{
		String sql = "insert into breed( breed_name, size, min_weight , max_weight , adult_min_age_year, " +
				"adult_min_age_month, senoir_min_age_year, senoir_min_age_month, neck_shape, face_shape ) " +
				"value( ?,?,?,?,?,?,?,?,?,? )";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, breed.getBreedName());
		ps.setString(2, breed.getSize());
		ps.setInt(3, breed.getMinWeight());
		ps.setInt(4, breed.getMaxWeight());
		ps.setInt(5, breed.getAdultMinAgeYear());
		ps.setInt(6, breed.getAdultMinAgeMonth());
		ps.setInt(7, breed.getSeniorMinAgeYear());
		ps.setInt(8, breed.getSeniorMinAgeMonth());
		ps.setString(9, breed.getNeckShape());
		ps.setString(10, breed.getFaceShape());

		ps.executeUpdate();
		ps.close();
		
		return true;
	}
	
	public Breed getBreedByName( String breedName ) throws Exception
	{
		Breed breed = new Breed();
		
		String sql = "select * from breed where breed_name=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, breedName);

		ResultSet rs = ps.executeQuery();

		if (rs.next())
		{
			breed.setBreedId( rs.getInt( "ID" ) );
			breed.setBreedName( rs.getString( "BREED_NAME" ) );
			breed.setSize( rs.getString( "size" ) );
			breed.setMinWeight( rs.getInt( "min_weight" ) );
			breed.setMaxWeight( rs.getInt( "max_weight" ) );
			breed.setAdultMinAgeYear(rs.getInt("adult_min_age_year"));
			breed.setAdultMinAgeMonth(rs.getInt("adult_min_age_month"));
			breed.setSeniorMinAgeYear(rs.getInt("senoir_min_age_year"));
			breed.setSeniorMinAgeMonth(rs.getInt("senoir_min_age_month"));
			breed.setNeckShape(rs.getString("neck_shape"));
			breed.setFaceShape(rs.getString("face_shape"));
		}

		rs.close();
		ps.close();
		
		return breed;
	}
	
	public int getBreedIdByName( String breedName ) throws Exception
	{
		int id = 0;
		
		String sql = "select id from breed where breed_name=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, breedName);

		ResultSet rs = ps.executeQuery();

		if (rs.next())
		{
			id = rs.getInt( "ID" );
		}

		rs.close();
		ps.close();
		
		return id;
	}

	public ArrayList<Breed> getBreedName() throws Exception
	{
		ArrayList<Breed> breedList = new ArrayList<Breed>();

		String sql = "select id, breed_name from breed";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			Breed breed = new Breed();
			breed.setBreedId( rs.getInt( "ID" ) );
			breed.setBreedName( rs.getString( "BREED_NAME" ) );
			
			breedList.add( breed );
		}

		rs.close();
		ps.close();

		return breedList;
	}

	public ArrayList<Breed> getAllBreed() throws Exception
	{
		ArrayList<Breed> breedList = new ArrayList<Breed>();

		String sql = "select * from breed";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			Breed breed = new Breed();
			breed.setBreedId( rs.getInt( "ID" ) );
			breed.setBreedName( rs.getString( "BREED_NAME" ) );
			breed.setSize( rs.getString( "size" ) );
			breed.setMinWeight( rs.getInt( "min_weight" ) );
			breed.setMaxWeight( rs.getInt( "max_weight" ) );
			breed.setAdultMinAgeYear(rs.getInt("adult_min_age_year"));
			breed.setAdultMinAgeMonth(rs.getInt("adult_min_age_month"));
			breed.setSeniorMinAgeYear(rs.getInt("senoir_min_age_year"));
			breed.setSeniorMinAgeMonth(rs.getInt("senoir_min_age_month"));
			breed.setNeckShape(rs.getString("neck_shape"));
			breed.setFaceShape(rs.getString("face_shape"));
			
			breedList.add( breed );
		}

		rs.close();
		ps.close();

		return breedList;
	}
	
	public Breed getBreedById( int id ) throws Exception
	{
		Breed breed = new Breed();
		
		String sql = "select * from breed where id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);

		ResultSet rs = ps.executeQuery();

		if (rs.next())
		{
			breed.setBreedId( rs.getInt( "ID" ) );
			breed.setBreedName( rs.getString( "BREED_NAME" ) );
			breed.setSize( rs.getString( "size" ) );
			breed.setMinWeight( rs.getInt( "min_weight" ) );
			breed.setMaxWeight( rs.getInt( "max_weight" ) );
			breed.setAdultMinAgeYear(rs.getInt("adult_min_age_year"));
			breed.setAdultMinAgeMonth(rs.getInt("adult_min_age_month"));
			breed.setSeniorMinAgeYear(rs.getInt("senoir_min_age_year"));
			breed.setSeniorMinAgeMonth(rs.getInt("senoir_min_age_month"));
			breed.setNeckShape(rs.getString("neck_shape"));
			breed.setFaceShape(rs.getString("face_shape"));
		}

		rs.close();
		ps.close();
		
		return breed;
	}
	
	public boolean updateBreed( Breed breed ) throws Exception
	{
		String sql = "UPDATE BREED SET BREED_NAME=?, SIZE=?, MIN_WEIGHT=?, MAX_WEIGHT=?, " +
				"ADULT_MIN_AGE_YEAR=?, ADULT_MIN_AGE_MONTH=?, SENOIR_MIN_AGE_YEAR=?, " +
				"SENOIR_MIN_AGE_MONTH=?, NECK_SHAPE=?, FACE_SHAPE=? " +
				"WHERE id=?";
		
//		System.out.println( "\n\n ==>" + breed.getBreedId() + "::" + breed.getBreedName() + "<== \n\n" );
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, breed.getBreedName());
		ps.setString(2, breed.getSize());
		ps.setInt(3, breed.getMinWeight());
		ps.setInt(4, breed.getMaxWeight());
		ps.setInt(5, breed.getAdultMinAgeYear());
		ps.setInt(6, breed.getAdultMinAgeMonth());
		ps.setInt(7, breed.getSeniorMinAgeYear());
		ps.setInt(8, breed.getSeniorMinAgeMonth());
		ps.setString(9, breed.getNeckShape());
		ps.setString(10, breed.getFaceShape());
		ps.setInt(11, breed.getBreedId());

		ps.executeUpdate();
		ps.close();
		
		conn.close();
		
		return true;
	}

}
